﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using DTO;

namespace DataAccessTier
{
    public class NhaCungCapDAO : DBConnection
    {
        public NhaCungCapDAO() : base() { }

        public DataTable GetAllNhaCungCap()
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "SELECT * FROM NHACUNGCAP";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }

        public bool kiemtratontai(string mancc)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            bool tatkt = false;
            SqlDataAdapter da_kiemtra = new SqlDataAdapter("SELECT * FROM NHACUNGCAP WHERE MANCC = '" + mancc + "'", conn);
            DataTable dt_kiemtra = new DataTable();
            da_kiemtra.Fill(dt_kiemtra);
            if (dt_kiemtra.Rows.Count > 0)
            {
                tatkt = true;
            }
            da_kiemtra.Dispose();
            conn.Close();
            return tatkt;
        }

        public bool Insert(NhaCungCap ncc)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "INSERT INTO NHACUNGCAP(MANCC,HOTEN,DIACHI,SODT,EMAIL,NODAUKY) VALUES('" + ncc.MANCC + "',N'" + ncc.HOTEN + "',N'" + ncc.DIACHI + "','" + ncc.SODT + "','" + ncc.EMAIL + "','" + ncc.NODAUKY + "')";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                conn.Close();
                return false;
            }
        }

        public bool Delete(string mancc)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "DELETE FROM NHACUNGCAP WHERE MANCC = '" + mancc + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                conn.Close();
                return false;
            }
        }

        public bool Update(NhaCungCap ncc)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "UPDATE NHACUNGCAP SET MANCC = '" + ncc.MANCC + "', HOTEN = N'" + ncc.HOTEN + "', DIACHI = N'" + ncc.DIACHI + "', SODT = '" + ncc.SODT + "', EMAIL = '" + ncc.EMAIL + "', NODAUKY = '" + ncc.NODAUKY + "' WHERE MANCC = '" + ncc.MANCC + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                conn.Close();
                return false;
            }
        }

        //Lấy danh sách tên hàng cho vào checkedlist
        public DataTable GetAllTenHangHoa()
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "SELECT * FROM HANGHOA ORDER BY MAHANG";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }

        //Lấy danh sách hàng hóa của nhà cung cấp để kiểm tra check trong checkedlistbox
        public DataTable GetDanhSachHH_CTHCC(string mancc)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "SELECT * FROM CHITIETHANGCC WHERE MANCC ='" + mancc + "' ORDER BY MAHANG";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }

        public DataTable GetSTTMaKhachHang()
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "SELECT TOP 1 STT FROM CHITIETHANGCC ORDER BY STT DESC";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable datatable = new DataTable();
                da.Fill(datatable);
                conn.Close();
                return datatable;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }

        public bool InsertTenHang(int stt, string mancc, string mahang, string tenhang)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "INSERT INTO CHITIETHANGCC(STT,MANCC,MAHANG,TENHANG) VALUES('" + stt + "','" + mancc + "','" + mahang + "',N'" + tenhang + "')";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                conn.Close();
                return false;
            }
        }

        public bool DeleteTenHang(string mancc)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "DELETE FROM CHITIETHANGCC WHERE MANCC = '" + mancc + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                conn.Close();
                return false;
            }
        }

        public bool kiemtratontaimanhacc(string mancc)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            bool tatkt = false;
            SqlDataAdapter da_kiemtra = new SqlDataAdapter("SELECT * FROM NHACUNGCAP WHERE MANCC LIKE N'" + "%" + @mancc + "%" + "'", conn);
            DataTable dt_kiemtra = new DataTable();
            da_kiemtra.Fill(dt_kiemtra);
            if (dt_kiemtra.Rows.Count > 0)
            {
                tatkt = true;
            }
            da_kiemtra.Dispose();
            conn.Close();
            return tatkt;
        }

        public bool kiemtratontaitennhacc(string tennhacc)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            bool tatkt = false;
            SqlDataAdapter da_kiemtra = new SqlDataAdapter("SELECT * FROM NHACUNGCAP WHERE HOTEN LIKE N'" + "%" + @tennhacc + "%" + "'", conn);
            DataTable dt_kiemtra = new DataTable();
            da_kiemtra.Fill(dt_kiemtra);
            if (dt_kiemtra.Rows.Count > 0)
            {
                tatkt = true;
            }
            da_kiemtra.Dispose();
            conn.Close();
            return tatkt;
        }

        //DataTable hàng hóa theo mã hàng hóa
        public DataTable GetAllNhaCCTheoMa(string mancc)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "SELECT * FROM NHACUNGCAP WHERE MANCC LIKE N'" + "%" + @mancc + "%" + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }

        //DataTable hàng hóa theo tên hàng hóa
        public DataTable GetAllNhaCCTheoTen(string tennhacc)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                string sql = "SELECT * FROM NHACUNGCAP WHERE HOTEN LIKE N'" + "%" + @tennhacc + "%" + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                conn.Close();
            }
            return null;
        }
    }
}
